package cn.chowx.myjfinal.statistics.model;

import com.google.common.base.Strings;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.chowx.myjfinal.common.BaseModel;
import cn.chowx.myjfinal.common.TableName;
import cn.chowx.myjfinal.help.PageHelp;

/**
 * 短信成功率, 计费成功率查询
 * @author zcqshine
 *
 */
public class SmsFeeRate extends BaseModel<SmsFeeRate> {
	private static final long serialVersionUID = 9197556682030705209L;
	public static final SmsFeeRate DAO = new SmsFeeRate();

	/**
	 * 按国家查询成功率
	 * @param pageHelp
	 * @return
	 */
	public Page<Record> findCountryRecordList(PageHelp pageHelp){
		String select = "select s.countryId,c.name countryName,"
				+ "SUM(s.smsSuccessCounts)smsSuccessCounts,"
				+ "SUM(s.smsUpCounts)smsUpCounts,"
				+ "(CASE SUM(s.smsSuccessCounts) WHEN 0 THEN 0 ELSE ROUND(SUM(s.smsUpCounts)/SUM(s.smsSuccessCounts),4)*100 END)smsUpSucessRate," //上行成功率
				+ "SUM(s.feeSuccessCounts)feeSuccessCounts,"
				+ "(CASE SUM(s.smsUpCounts) WHEN 0 THEN 0 ELSE ROUND(SUM(s.feeSuccessCounts)/SUM(s.smsUpCounts),4)*100 END)downSuccessRate, "	//下行成功率
				+ "(CASE SUM(s.smsSuccessCounts) WHEN 0 THEN 0 ELSE ROUND(SUM(s.feeSuccessCounts)/SUM(s.smsSuccessCounts),4)*100 END) feeSuccessRate," //计费成功率
				+ "s.separatorDate";
		StringBuilder sb = new StringBuilder();
		sb.append("FROM ").append(TableName.StatisticsSmsFee.getTableName()).append(" s,")
			.append(TableName.Country.getTableName()).append(" c ")
			.append(" WHERE s.countryId = c.id");
		String searchStr = PageHelp.buildSearchSQL(pageHelp, "s");
		sb.append(searchStr);
		sb.append(" GROUP BY s.countryId ,s.separatorDate");
		if(!Strings.isNullOrEmpty(pageHelp.getSidx())){
			sb.append(" order by s.").append(pageHelp.getSidx()).append(" ").append(pageHelp.getSord());
		}else{
			sb.append(" order by s.separatorDate,s.countryId DESC");
		}
		
		Page<Record> records = Db.paginate(pageHelp.getPageNum(), pageHelp.getRows(), select, sb.toString());
		return records;
	}
	
	/**
	 * 根据国家 ID 获取该国家下各个运营商的短信,付费数据
	 * @param pageHelp
	 * @param countryId	国家 ID
	 * @return
	 */
	public Page<Record> findCountryProviderRecordList(PageHelp pageHelp,int countryId){
		String select = "select s.operatorId, o.name operatorName,s.tariff,s.cmdPort,s.separatorDate,s.spId, sp.spName,"
				+ "SUM(s.smsSuccessCounts)smsSuccessCounts,"
				+ "SUM(s.smsUpCounts)smsUpCounts,"
				+ "(CASE SUM(s.smsSuccessCounts) WHEN 0 THEN 0 ELSE ROUND(SUM(s.smsUpCounts)/SUM(s.smsSuccessCounts),4)*100 END)smsUpSucessRate," //上行成功率
				+ "SUM(s.feeSuccessCounts)feeSuccessCounts,"
				+ "(CASE SUM(s.smsUpCounts) WHEN 0 THEN 0 ELSE ROUND(SUM(s.feeSuccessCounts)/SUM(s.smsUpCounts),4)*100 END)downSuccessRate, "	//下行成功率
				+ "(CASE SUM(s.smsSuccessCounts) WHEN 0 THEN 0 ELSE ROUND(SUM(s.feeSuccessCounts)/SUM(s.smsSuccessCounts),4)*100 END) feeSuccessRate "; //计费成功率
		StringBuilder sb = new StringBuilder();
		sb.append("FROM ").append(TableName.StatisticsSmsFee.getTableName()).append(" s LEFT JOIN ")
			.append(TableName.Operator.getTableName()).append(" o ON s.operatorId = o.id ")
			.append(" LEFT JOIN ").append(TableName.SpInfo.getTableName()).append(" sp ON s.spId = sp.id ")
			.append(" WHERE 1=1 ");
		sb.append(PageHelp.buildSearchSQL(pageHelp, "s"));
		sb.append(" GROUP BY s.operatorId,s.cmdPort,s.separatorDate");
		if(!Strings.isNullOrEmpty(pageHelp.getSidx())){
			sb.append(" order by s.").append(pageHelp.getSidx()).append(" ").append(pageHelp.getSord());
		}else{
			sb.append(" order by s.separatorDate,s.operatorId DESC");
		}
//		System.out.println(select + " " + sb.toString());
		Page<Record> records = Db.paginate(pageHelp.getPageNum(), pageHelp.getRows(), select, sb.toString());
		return records;
	}
	
	/**
	 * 渠道成功率查询
	 * @param pageHelp
	 * @return Page<Record>
	 */
	public Page<Record> findChannelRecordList(PageHelp pageHelp){
		String select = "select s.separatorDate,s.operatorId,op.name,s.channelId, ch.name channelName,s.productId, p.name productName,s.spId, sp.spName, s.countryId, c.name countryName, s.tariff,"
				+ "SUM(s.smsSuccessCounts)smsSuccessCounts,"
				+ "SUM(s.smsCounts)smsCounts,"
				+ "(CASE SUM(s.smsCounts) WHEN 0 THEN 0 ELSE ROUND(SUM(s.smsSuccessCounts)/SUM(s.smsCounts),4)*100 END) smsSendRate,"
				+ "SUM(s.smsUpCounts)smsUpCounts,"
				+ "(CASE SUM(s.smsSuccessCounts) WHEN 0 THEN 0 ELSE ROUND(SUM(s.smsUpCounts)/SUM(s.smsSuccessCounts),4)*100 END)smsUpSucessRate,"
				+ "SUM(s.feeSuccessCounts)feeSuccessCounts,"
				+ "(CASE SUM(s.smsUpCounts) WHEN 0 THEN 0 ELSE ROUND(SUM(s.feeSuccessCounts)/SUM(s.smsUpCounts),4)*100 END)downSuccessRate, "	//下行成功率
				+ "(CASE SUM(s.smsSuccessCounts) WHEN 0 THEN 0 ELSE ROUND(SUM(s.feeSuccessCounts)/SUM(s.smsSuccessCounts),4)*100 END) feeSuccessRate,"
				+ "SUM(s.feeSuccessAmount) feeSuccessAmount ";
		StringBuilder sb = new StringBuilder();
		sb.append(" FROM ").append(TableName.StatisticsSmsFee.getTableName()).append(" s LEFT JOIN ")
			.append(TableName.Channel.getTableName()).append(" ch ON s.channelId = ch.id LEFT JOIN ")
			.append(TableName.SpInfo.getTableName()).append(" sp ON s.spId = sp.id LEFT JOIN ")
			.append(TableName.Country.getTableName()).append(" c ON s.countryId = c.id LEFT JOIN ")
			.append(TableName.Product.getTableName()).append(" p ON s.productId = p.id LEFT JOIN ")
			.append(TableName.Operator.getTableName()).append(" op ON s.operatorId = op.id ")
			.append(" WHERE 1=1 ");
		sb.append(PageHelp.buildSearchSQL(pageHelp, "s"));
		sb.append(" GROUP BY s.channelId, s.separatorDate, s.productId, s.spId,s.countryId,s.tariff ");
		if(!Strings.isNullOrEmpty(pageHelp.getSidx())){
			sb.append(" order by s.").append(pageHelp.getSidx()).append(" ").append(pageHelp.getSord());
		}else{
			sb.append(" order by s.separatorDate,s.channelId DESC");
		}
		Page<Record> records = Db.paginate(pageHelp.getPageNum(), pageHelp.getRows(), select, sb.toString());
		return records;
	}
}
